package com.yayan.beautyclub.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.yayan.beautyclub.bean.Ticket;

public class TicketDao extends ConnectionBase implements ITicketDao {

	@Override
	public List<Ticket> getTicketList(int page, int size) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select * from tickets where status=1 limit " + (page-1)*size + "," + size;
		String sqlNum = "select COUNT(*) as status from tickets where status=1";
		QueryRunner qRunner = new QueryRunner();
		Ticket ticket = (Ticket)qRunner.query(getConn(), sqlNum, new BeanHandler(Ticket.class));
		List<Ticket> list = (List<Ticket>)qRunner.query(getConn(), sqlStr, new BeanListHandler(Ticket.class));
		list.add(0, ticket);
		return list;
	}

	@Override
	public Ticket getTicket(long tId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select * from tickets where tId=?";
		QueryRunner qRunner = new QueryRunner();
		Ticket ticket = (Ticket)qRunner.query(getConn(), sqlStr, new BeanHandler(Ticket.class), tId);
		return ticket;
	}

	@Override
	public long save(Ticket ticket) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "insert into tickets(tName,description,startTime,endTime,time,image) values(?,?,?,?,?,?)";
		Connection conn = getConn();
		conn.setAutoCommit(false);
		PreparedStatement ps = conn.prepareStatement(sqlStr, Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, ticket.gettName());
		ps.setString(2, ticket.getDescription());
		ps.setDate(3, new java.sql.Date(ticket.getStartTime().getTime()));
		ps.setDate(4, new java.sql.Date(ticket.getEndTime().getTime()));
		ps.setString(5, new java.sql.Timestamp(ticket.getTime().getTime()).toString());
		ps.setString(6, ticket.getImage());
		int re = ps.executeUpdate();
		ResultSet rsKey = ps.getGeneratedKeys();
		long r = 0;
		while(re != 0 && rsKey.next()){
			r = rsKey.getLong(1);
		}
		conn.commit();
		conn.setAutoCommit(true);
		close(conn,ps,null);
		return r;
	}

	@Override
	public boolean update(Ticket ticket) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "update tickets set tName=?,description=?,startTime=?,endTime=?,time=?,image=? where tId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setString(1, ticket.gettName());
		ps.setString(2, ticket.getDescription());
		ps.setDate(3, new java.sql.Date(ticket.getStartTime().getTime()));
		ps.setDate(4, new java.sql.Date(ticket.getEndTime().getTime()));
		ps.setString(5, new java.sql.Timestamp(ticket.getTime().getTime()).toString());
		ps.setString(6, ticket.getImage());
		ps.setLong(7, ticket.gettId());
		boolean r = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return r;
	}

	@Override
	public boolean delete(long tId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "update tickets set status=0 where tId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setLong(1, tId);
		boolean r = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return r;
	}

}
